package com.lingxu.module.collection.service.impl;

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSON;
import com.deepoove.poi.XWPFTemplate;
import com.lingxu.base.common.util.*;
import com.lingxu.module.collection.entity.*;
import com.lingxu.module.collection.mapper.ReportMapper;
import com.lingxu.module.collection.service.IReportService;
import com.lingxu.module.redList.entity.RedList;
import com.lingxu.module.redList.mapper.RedListMapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Clob;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class ReportService implements IReportService {

    private static final Logger logger = LoggerFactory.getLogger(ReportService.class);

    @Autowired
    private ReportMapper reportMapper;
    @Autowired
    private RedListMapper redListMapper;

    @Value(value = "${uploadUrl}")
    private String uploadpath;
    @Value(value = "${isEncrypt}")
    private String isEncrypt;

    @Override
    public void exportReport(ReportVo report, HttpServletResponse response,String timestamp) {
        try  {
            String filePath = uploadpath + "/report/";
            Map<String,Object> map = getMapinfoNew(report,filePath);
            String modelname = (String) map.get("modelname");
            String fileName = modelname + "_" +System.currentTimeMillis() + ".doc";
            createWordFile(map,filePath + fileName);
            List<ModeResultList> resultLists = (List<ModeResultList>) map.get("modeResultList");
            if(resultLists != null && resultLists.size() > 0){
                //多个文件打成zip下载
                String word = filePath + fileName;
                ArrayList<File> fileList = new ArrayList<>();
                File wordFile = new File(word);
                fileList.add(wordFile);
                for (ModeResultList mr : resultLists){
                    if(!StringUtils.isEmpty(mr.getExcelFiles())){
                        File file = new File(filePath + mr.getExcelFiles());
                        fileList.add(file);
                    }
                }
                String zipName = filePath + modelname + "_" +System.currentTimeMillis() + ".zip";
                //区分是否需要加密压缩包
                if("1".equals(isEncrypt)){
                    ZipUtils.zipFilePwd(zipName,fileList,timestamp);
                }else {
                    OutputStream outputStream = new FileOutputStream(zipName);
                    ZipUtils.toZip(fileList,outputStream);
                }
                FileUtils.downloaddocx(zipName,response, false);
                //删除文件
                File zip = new File(zipName);
                zip.delete();
                wordFile.delete();
                for (ModeResultList mr : resultLists){
                    if(!StringUtils.isEmpty(mr.getExcelFiles())){
                        new File(filePath + mr.getExcelFiles()).delete();
                    }
                }
            } else {
                FileUtils.downloaddocx(filePath + fileName,response, true);
                File file = new File(filePath + fileName);
                file.delete();
            }
        } catch (Exception e) {
            logger.error("文件转换异常：",e);
        }
    }

    private Map<String, Object> getMapinfoNew(ReportVo report,String filePath) throws SQLException, IOException {
        String modelid = report.getModelid();
        String userid= report.getUserid();
        Map<String,Object> map = new HashMap<>();
        //根据模型id查询基本信息
        ReportDetail detail = reportMapper.findModelDetail(modelid);
        String time= DateUtil.formatDate(new Date());
        String date = time.substring(0,4) + "年" + time.substring(5,7) + "月" + time.substring(8,10) + "日";
        map.put("modelname",detail.getMastermodelname());
        map.put("date",date);
        map.put("username",detail.getRegionName() + "-" + detail.getName());
        map.put("areaname",detail.getRegionName());
        Map mm = reportMapper.getDataFroms(detail.getMastermodelid());
        if(mm != null){
            String dataFroms = ToolUtil.clobToString((Clob) mm.get("DATAFROMS"));
            //数据来源
            if(!StringUtils.isBlank(dataFroms)){
                map.put("froms",dataFroms);
            }else {
                map.put("froms","暂未填写");
            }
            String qlfxd = (String) mm.get("QLFXD");
            map.put("ypmd",qlfxd);

        }

        map.put("mxms",detail.getMastermodelremark());
        //比对规则
        StringBuilder content = new StringBuilder();
        List<String> jdgz = reportMapper.findModelJdgz(detail.getMastermodelid());
        for(String s : jdgz){
            content.append(s).append(";");
        }
        if(!StringUtils.isEmpty(content)) {
            content.replace(content.lastIndexOf(";"),content.length(),"。");
            map.put("content", content);
        }
        //判断是否首次生成编码
        if(StringUtils.isEmpty(detail.getReportno())){
            //首次生成编码
            String reportno = reportNoHandler(detail);
            //更新表
            reportMapper.updateReportNo(report.getModelid(),reportno);
            map.put("reportno",reportno);
        }else {
            map.put("reportno",detail.getReportno());
        }
        //子模型id合集
        List<String> ids = Arrays.asList(detail.getLinkmodelid().split(","));
        //查询子模型信息计算求和
        List<ReportInfo> reportInfo = reportMapper.getReportInfo(ids);
        //红名单数量
        int rednum =0;
        int hmdqx = reportMapper.getHmdAuthorityByUserId(userid);
        int dataNum = 0,resultNum = 0;

        StringBuilder allResultInfos = new StringBuilder();
        int x = 1;
        for(ReportInfo rr : reportInfo){
            int red = 0;
            if(hmdqx == 0){
                red = getRedNum(rr.getId());
                rednum += red;
            }
            dataNum += rr.getDatanum();
            resultNum += rr.getResultnum();
            allResultInfos.append("其中针对").append(rr.getRemark()).append("的权利风险点，涉及数据量").append(rr.getDatanum())
                    .append("条，发现问题数据").append(rr.getResultnum()).append("条");
            //红名单数量为0不显示
            if(red < 1){
                allResultInfos.append(";");
            }else {
                allResultInfos.append("，红名单涉及").append(red).append("人;");
            }

        }
        //红名单数量为0不显示
        if(rednum < 1){
            map.put("rednum", "");
        }else {
            map.put("rednum", "其中，红名单涉及" + rednum + "人。");
        }
        map.put("datanum",dataNum);
        //涉及表数量
        int tableNum = reportMapper.getTableNum(ids);
        map.put("tablenum",tableNum);
        map.put("resultnum",resultNum);
        //子模型数量两个以上的时候添加此段说明
        if(reportInfo.size() > 1){
            map.put("allResultInfos",allResultInfos);
        }else {
            map.put("allResultInfos","");
        }
        List<ModeResultList> resultList = new ArrayList<>();
        //查询模型运行记录
        for(String id : ids) {
            ModelRunRecord modelRunRecord = reportMapper.getModelRunRecordByModelId(id);
            if (modelRunRecord == null) {
                continue;
            }
            String resulttable = modelRunRecord.getResulttable();
            String resultcolumns = modelRunRecord.getResultcolumns();
            List<ModelColumnInfo> modelColumnInfoList = JSON.parseArray(resultcolumns, ModelColumnInfo.class);
            Map<String, String> head = new LinkedHashMap<>();
            if (modelColumnInfoList.size() > 0) {
                for (ModelColumnInfo modelColumnInfo : modelColumnInfoList) {
                    head.put(modelColumnInfo.getEnname().toUpperCase(), modelColumnInfo.getCnname());
                }

                //查询总数
                String sqlCou = "select count(*) from DIMENRESULT." + resulttable;
                Integer cou = reportMapper.getResultCount(sqlCou);
                //查询列表前20
                String[] objects = head.keySet().toArray(new String[0]);
                List<String> list = new ArrayList<>(Arrays.asList(objects));
                String columns = StringUtils.join(list, ",");
                String sql = "select " + columns + ", '否' as ISRED" + " from DIMENRESULT." + resulttable;

                //新增默认字段是否红名单
                head.put("ISRED","是否红名单");

                List<LinkedHashMap<String, Object>> dataList = reportMapper.getResultList(sql);
/*                //判断核查表是否存在
                String sql3 = "SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME =" +  resulttable +"_chack";
                Integer isExist = reportMapper.getResultCount(sql3);
                if (isExist> 0){
                    //加上已核查
                    String sql2 = "select " + columns + ", '否' as ISRED" + " from DIMENRESULT." + resulttable +"_chack";
                    dataList.addAll(reportMapper.getResultList(sql2));
                    String sqlCou2 = "select count(*) from DIMENRESULT." + resulttable+"_chack";
                    cou += reportMapper.getResultCount(sqlCou2);
                }*/


                //用户存放红名单人员信息
                List<LinkedHashMap<String, Object>> contain = new ArrayList<>();

                //查询红名单
                List<RedList> redLists = redListMapper.openFindRedListApi(null);
                //遍历去除红名单数据
                Iterator<LinkedHashMap<String, Object>> iterator = dataList.iterator();
                while (iterator.hasNext()) {
                    LinkedHashMap<String, Object> m = iterator.next();
                    if(m != null && m.size() > 0){
                        for (String keys : m.keySet()) {
                            if (keys.contains("SFZ") || keys.contains("ZJHM")) {
                                String values = (String) m.get(keys);
                                for (RedList red : redLists) {
                                    String idcard = red.getIdcard();
                                    if (idcard.equals(values)) {
                                        //放入红名单人员list
                                        contain.add(m);
                                        iterator.remove();
                                        System.out.println("过滤红名单数据：" + idcard);
                                        break;
                                    }
                                }
                                break;
                            }
                        }
                    }
                }

                //判断是否通过红名单人员查看权限
                String redtotal = modelRunRecord.getRedtotal();
                if(!StringUtils.isEmpty(redtotal)){
                    int red = Integer.parseInt(redtotal);
                    String state = modelRunRecord.getApprovestate();
                    //红名单人员数量大于0并且通过审批
                    if(red > 0 && "1".equals(state)){
                        for(LinkedHashMap<String, Object> m : contain){
                            m.put("ISRED","是");
                        }
                        dataList.addAll(contain);
                    }
                }


                StringBuilder sb = new StringBuilder("1、");
                int index = 1;
                if (dataList.size() > 0) {
                    List<LinkedHashMap<String, Object>> mapList;
                    if (dataList.size() > 20) {
                        mapList = dataList.subList(0, 10);
                    } else {
                        mapList = dataList;
                    }
                    for (Map maps : mapList) {
                        if(maps != null && maps.size() > 0) {
                            for (String key : head.keySet()) {
                                Object o = maps.get(key.toUpperCase());
                                //判断为姓名和身份证号码的字符串进行拼接
                                if (key.contains("xm")) {
                                    sb.append(head.get(key)).append(":").append(o).append(",");
                                    break;
                                }
                            }
                            for (String key : head.keySet()) {
                                Object o = maps.get(key.toUpperCase());
                                //判断为姓名和身份证号码的字符串进行拼接
                                if (key.contains("sfz") || key.contains("zjhm")) {
                                    sb.append(head.get(key)).append(":").append(o).append(",");
                                    break;
                                }
                            }
                            for (String key : head.keySet()) {
                                Object o = maps.get(key.toUpperCase());
                                //判断为姓名和身份证号码的字符串进行拼接
                                if (key.contains("dwmc")) {
                                    sb.append(head.get(key)).append(":").append(o).append(",");
                                    break;
                                }
                            }
                            for (String key : head.keySet()) {
                                Object o = maps.get(key.toUpperCase());
                                //判断为姓名和身份证号码的字符串进行拼接
                                if (key.contains("zw")) {
                                    sb.append(head.get(key)).append(":").append(o).append(",");
                                }
                            }
                        }
                        if (sb.length() > 2) {
                            sb.replace(sb.lastIndexOf(","),sb.length(),";");
                            index++;
                            if (index <= mapList.size()) {
                                sb.append("\n").append("    ").append(index).append("、");
                            }
                        }
                    }

                    ModeResultList modeResultList = new ModeResultList();

                    if (sb.length() > 2) {
//                        String infos = sb.toString();
                        modeResultList.setInfos(sb.replace(sb.lastIndexOf(";"),sb.length(),"。").toString());
                    } else {
                        modeResultList.setInfos("无法匹配姓名身份证字段");
                    }
                    //查询模型信息
                    List<String> mid = new ArrayList<>();
                    mid.add(id);
                    List<ReportInfo> de = reportMapper.getReportInfo(mid);
                    if (de.size() > 0) {
                        modeResultList.setModel(de.get(0).getRemark());
                        modeResultList.setModelnum(de.get(0).getDatanum());
                    }
                    //excel附件
                    if (cou > 0) {
                        modeResultList.setInto(cou);
                        modeResultList.setExist("部分问题数据如下：");
//                        String excelFileName = System.currentTimeMillis() + ".xls";
                        String excelFileName = de.get(0).getModelname() + ".xls";
                        //保存Excel文件,剔除全NULL数据
                        dataList.removeAll(Collections.singleton(null));
                        saveExcel(dataList, head, filePath + excelFileName);
                        modeResultList.setExcelFiles(excelFileName);
                        modeResultList.setExcel("详细完整数据请看附件：" + excelFileName);
                    }
                    resultList.add(modeResultList);
                }
            }
        }
        map.put("modeResultList",resultList);
        return map;
    }

    //保存Excel
    private void saveExcel(List<LinkedHashMap<String,Object>> dataAll,Map<String,String> headMap,String fileName){
        //要导出的动态列
        List<ExcelExportEntity> beanList = new ArrayList<>();
        for(Map.Entry<String,String> map : headMap.entrySet()){
            beanList.add(new ExcelExportEntity(map.getValue(),map.getKey().toUpperCase()));
        }
        EasyExcelUtils.writeExcelToStream(fileName,beanList,dataAll);
    }


    //生成报告编码
    private String reportNoHandler(ReportDetail detail) {
        //年月
        String yeardate = new SimpleDateFormat("yyyyMM").format(new Date());
        //查询当前地区本月生成模型数量
        String no = "";
        String number = reportMapper.findCountByRegionAndMonth(detail.getRegionName(),yeardate);
        if(StringUtils.isEmpty(number)){
            no = "01";
        }else {
            String s = number.substring(6);
            int count = Integer.parseInt(s);
            if(count < 10){
                no = "0" + count;
            } else {
                no = count + "";
            }
        }
        return yeardate + no;
    }

    private int getRedNum(String modelid) {
        int rednum = 0;
        ModelRunRecord modelRunRecord = reportMapper.getModelRunRecordByModelId(modelid);
        if(modelRunRecord == null){
            return rednum;
        }
        String resulttable = modelRunRecord.getResulttable();
        String sql="select * from DIMENRESULT." + resulttable;
        List<LinkedHashMap<String,Object>> dataList = reportMapper.getResultList(sql);
        if(dataList!=null && dataList.size()>0){
            List<Map<String, Object>> dataHandleList = new ArrayList<>();
            List<RedList> redLists=reportMapper.getRedListAll();
            StringBuffer stringBuffer = new StringBuffer();
            if(redLists != null && redLists.size() > 0) {
                for (RedList redList : redLists) {
                    if(StringUtils.isNotBlank(redList.getIdcard())) {
                        stringBuffer.append(redList.getIdcard()+",");
                    }
                }
                //循环模型运行结果
                if(dataList.size() > 0) {
                    for (Map<String, Object> dataMap : dataList) {
                        int redTotal = 0;
                        Iterator<Map.Entry<String, Object>> it = dataMap.entrySet().iterator();
                        while(it.hasNext()){
                            Map.Entry<String, Object> entry = it.next();
                            //判断身份证号字段
                            if(entry.getKey().contains("sfzh")) {
                                if(stringBuffer.length() > 0) {
                                    if(stringBuffer.toString().contains(String.valueOf(entry.getValue()))) {
                                        redTotal++;
                                        break;
                                    }
                                }
                            }
                        }
                        if(redTotal == 0) {
                            dataHandleList.add(dataMap);
                        }
                    }
                }
                rednum = dataList.size() - dataHandleList.size();
            }
        }
        return rednum;
    }

    private void createWordFile(Map<String,Object> map, String filePath){
        FileOutputStream fos = null;
        XWPFTemplate template = null;
        try {
            Resource resource = new ClassPathResource("template/报告模板.docx");
            ZipSecureFile.setMinInflateRatio(0.001);
            template = XWPFTemplate.compile(resource.getInputStream()).render(map);
            fos = new FileOutputStream(FileUtil.checkExist(filePath));
            template.write(fos);
        }catch (IOException e){
            logger.error("文件创建失败",e);
        }finally {
            if(template != null){
                try {
                    template.close();
                } catch (IOException e) {
                    logger.error("关闭异常");
                }
            }
            if(fos != null){
                try {
                    fos.close();
                } catch (IOException e) {
                    logger.error("关闭异常");
                }
            }
        }
    }


    @Override
    public void restReportNo() {
        reportMapper.restReportNo();
    }
}
